﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TaxBusiness.Model;
using Dapper;

namespace TaxBusiness.DAL
{
    public class UsersDB
    {
        private string _selectAll = "SELECT * FROM [tb_Users] order by AuthID";
        private string _selectByName = "SELECT * FROM [tb_Users] WHERE UserName=@UserName";
        private string _insert = "INSERT INTO [tb_users]" +
                               " ([UserName]" +
                               ",[PWD]" +
                               ",[TrueName]" +
                               ",[OfficeID]" +
                               ",[AuthID]" +
                               ",[MenuID])" +
                           " VALUES " +
                              "(@UserName" +
                              ",@PWD" +
                              ",@TrueName" +
                              ",@OfficeID" +
                              ",@AuthID" +
                              ",@MenuID)";
        private string _getid = "SELECT CAST(SCOPE_IDENTITY() as int)";
        private string _insertUsersMenu = "Insert into [UsersMenu] (UserID,MenuID) values (@UserID,@MenuID)";
        private string _update = "UPDATE [tb_users] SET TrueName=@TrueName,OfficeID=@OfficeID,AuthID=@AuthID,MenuID=@MenuID WHERE ID=@ID";
        private string _deleteMenuItem = "DELETE FROM [UsersMenu] Where UserID=@UserID";
        private string _updateActivate = "UPDATE [tb_users] SET Activate=@Activate WHERE ID in @ID";
        private string _updatePWD = "UPDATE [tb_users] SET PWD=@PWD WHERE ID=@ID";
        private string _updateOffice = "UPDATE [tb_users] SET OfficeID=@OfficeID WHERE ID=@ID";
        private string _delete = "DELETE FROM [tb_users] where ID in @ID";

        public List<Model.Users> GetAll()
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Query<Model.Users>(_selectAll).ToList();
            }
        }

        public Model.Users Get(string name)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                var result = cn.Query<Model.Users>(_selectByName, new { UserName = name }).ToList();
                if (result.Count > 0)
                {
                    return result.First();
                }
                else
                {
                    return null;
                }
            }
        }

        public int Insert(Model.Users users, int[]menuItems)
        {
            int userid = 0;
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                cn.Open();
                IDbTransaction transacation = cn.BeginTransaction();
                try
                {
                    userid = cn.Query<int>(_insert + _getid, users,transacation).FirstOrDefault();
                    foreach (int menuid in menuItems)
                    {
                        cn.Execute(_insertUsersMenu, new { Userid = userid, MenuID = menuid }, transacation);
                    }
                    transacation.Commit();
                }
                catch (Exception e)
                {
                    transacation.Rollback();
                    throw new Exception(e.Message);
                }
            }
            return userid;
        }

        public int Update(Users users, int[] menuItems)
        {
            int i = 0; ;
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                cn.Open();
                IDbTransaction transacation = cn.BeginTransaction();
                try
                {
                    i = cn.Execute(_update, users, transacation);
                    cn.Execute(_deleteMenuItem, new { UserID = users.ID }, transacation);
                    foreach (int menuid in menuItems)
                    {
                        cn.Execute(_insertUsersMenu, new { Userid = users.ID, MenuID = menuid }, transacation);
                    }
                    transacation.Commit();
                }
                catch (Exception e)
                {
                    transacation.Rollback();
                    throw new Exception(e.Message);
                }
            }
            return i;
        }


        public int SetActivate(int[] id, bool isactivate)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_updateActivate, new { ID = id, Activate = isactivate });
            }
        }

        public int SetPWD(int id, string pwd)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_updatePWD, new { ID = id, PWD = pwd });
            }
        }

        public int SetOffice(int id, int officeid)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_updateOffice, new { ID = id, officeID = officeid });
            }
        }

        public int Delete(int[] id)
        {
            using (var cn = new SqlConnection(SqlConnectString.CONNSTRING))
            {
                return cn.Execute(_delete, new { ID = id});
            }
        }
    }
}
